package jdbcmapper;

import java.sql.Connection;
import java.sql.ResultSet;
import java.util.Hashtable;
import java.util.Vector;

import lotus.domino.*;
import java.sql.*;

/**
 * <p>Title: JDBC Mapper</p>
 *
 * <p>Description: Libraries to copy documents form Lotus Notes to jdbc
 * table</p>
 *
 * <p>Copyright: Copyright (c) 2005</p>
 *
 * <p>Company: The Salvation ARmy</p>
 *
 * @author Ansel Robateau
 * @version 1.1
 */
public class DeleteManager {
    private Connection m_con;
    private Vector m_primarykeys;
    private Hashtable m_docs;
    public DeleteManager(Connection con,String tablename,Vector primarykeys) {
        this.m_con = con;
        this.m_primarykeys = primarykeys;
        this.m_docs = new Hashtable();
    }

    /**
     * removeDeletedDocuments
     *
     * @param table Table
     */
    public void removeDeletedDocuments(Table table) throws Exception {
        NotesDB database = table.getParent().getParent().getParent();
        Database db = table.getSession().getDatabase(
            database.getString(NotesDB.SERVER),database.getString(NotesDB.FILENAME));
        String searchstr = "Form = \""+table.getParent().getParent().getName()+
                           "\"";
        System.out.println();
        //System.out.println(searchstr);
        //View view = db.getView(Table.ALLDOCUMENTSBYDATE);
        //ViewEntryCollection vcol = view.getAllEntriesByKey(table.getParent().getParent().getName().toLowerCase());
        DocumentCollection col = db.search(searchstr);
        String l = "Comparing " + col.getCount() + " documents";
        System.out.println(l);
        table.getM_log().add(l);
        //ViewEntry entry = vcol.getFirstEntry();
        Document doc = col.getFirstDocument();
        Hashtable docreference = null;
        //Create a searchable list of notes documents
        while(doc != null){
              NotesDoc ndoc = new NotesDoc(table.getSession(), doc);
              Object parentkey = null;
              for(int i = 0; i < this.m_primarykeys.size();i++){
                  FieldMap field = (FieldMap)this.m_primarykeys.get(i);
                  Object keyvalue = this.getValue(ndoc,field);
                  if( i > 0){
                      Hashtable ref = new Hashtable();
                      docreference.put(parentkey,ref);
                      ref.put(keyvalue,ndoc);
                      docreference = ref;
                  }else{
                      this.m_docs.put(keyvalue, ndoc);
                      docreference = this.m_docs;
                  }
                  parentkey = keyvalue;
              }
              doc = col.getNextDocument(doc);
        }
        //Get a list of the primary keys in the table
        String sql = this.getSearchSQL(table);
        System.out.println(sql);
        ResultSet rs = this.m_con.createStatement().executeQuery(sql);
        //Compare against the list of notes documents
        Vector docstodelete = new Vector();
        while(rs.next()){
            Hashtable ref = this.m_docs;
            Hashtable keys = new Hashtable();
            for(int i = 0;i < this.m_primarykeys.size();i++){
                FieldMap field = (FieldMap)this.m_primarykeys.get(i);
                Object key = this.getValue(rs,field);
                keys.put(field.getString(FieldMap.SQLFIELDNAME),key);
                Object val = ref.get(key);
                if(val == null){
                    //There is no match.
                    //This document will be deleted.
                    docstodelete.add(keys);
                }else if(val instanceof NotesDoc){
                    //There is a match.
                    //This document will not be deleted.
                }else if(val instanceof Hashtable){
                    ref = (Hashtable)val;
                }
            }
        }
        rs.close();
        System.out.println("There are " + docstodelete.size() + " document(s) to be deleted");
        table.getM_log().add("There are " + docstodelete.size() + " document(s) to be deleted");
        if(docstodelete.size() > 0){
            this.deleteDocuments(docstodelete, table);
        }
    }

    /**
     * deleteDocuments
     *
     * @param docstodelete Vector
     */
    private void deleteDocuments(Vector docstodelete,Table table)throws Exception {
        Vector keys = table.getPrimaryKeys();
        String sql = "delete from " + table.getString(Table.SQLTABLE);
        sql += " where ";
        int i = 0;
        while(i < keys.size()){
            FieldMap field = (FieldMap)keys.get(i);
            Object key = field.getString(FieldMap.SQLFIELDNAME);
            if ( i > 0 ){
                sql += " and ";
            }
            sql += key.toString() + " = ?";
            i++;
        }
        if(table.getBoolean(Table.bool_MULTIFORM) == true){
            if(table.hasFormValue()){
                sql += " form = ?";
            }
        }
        System.out.println(sql);
        for(i = 0; i < docstodelete.size();i++){
            PreparedStatement ps = this.m_con.prepareStatement(sql);
            Hashtable values = (Hashtable)docstodelete.get(i);
            for(int j = 0; j < table.getPrimaryKeys().size();j++){
                FieldMap field = (FieldMap)table.getPrimaryKeys().get(j);
                ps.setObject(j+1, values.get(field.getString(FieldMap.SQLFIELDNAME)));
            }
            if(table.getBoolean(Table.bool_MULTIFORM) == true && table.hasFormValue()){
                ps.setObject(table.getPrimaryKeys().size()+1,table.getParent().getParent().getName());
            }
            ps.execute();
            ps.close();
        }
    }

    /**
     * getValue
     *
     * @param ndoc NotesDoc
     * @param field FieldMap
     * @return Object
     */
    private Object getValue(NotesDoc ndoc, FieldMap field) throws Exception {
        if(field.getString(FieldMap.NOTESFIELDTYPE).equals(FieldMap.type_DATE)){
            return ndoc.getDate(field.getString(FieldMap.NOTESFIELDNAME));
        }else if(field.getString(FieldMap.NOTESFIELDTYPE).equals(FieldMap.type_DOUBLE)){
            return ndoc.getDouble(field.getString(FieldMap.NOTESFIELDNAME));
        }else if(field.getString(FieldMap.NOTESFIELDTYPE).equals(FieldMap.type_INT)){
            return ndoc.getInteger(field.getString(FieldMap.NOTESFIELDNAME));
        }else if(field.getString(FieldMap.NOTESFIELDTYPE).equals(FieldMap.type_TEXT)){
            return ndoc.getString(field.getString(FieldMap.NOTESFIELDNAME));
        }else if(field.getString(FieldMap.NOTESFIELDTYPE).equals(FieldMap.type_UID)){
            return ndoc.getDocument().getUniversalID();
        }
        return null;
    }
    private Object getValue(ResultSet rs,FieldMap field) throws Exception {
        String sqlfieldname = field.getString(FieldMap.SQLFIELDNAME);
        if(field.getString(FieldMap.SQLFIELDTYPE).equals(FieldMap.type_DATE)){
            java.sql.Date retval = null;
            retval = rs.getDate(sqlfieldname);
            if(retval != null) return new java.util.Date(retval.getTime());
            return null;
        }else if(field.getString(FieldMap.SQLFIELDTYPE).equals(FieldMap.type_DOUBLE)){
            try{
                double d = rs.getDouble(sqlfieldname);
                return new Double(d);
            }catch(Exception e){
                return null;
            }
        }else if(field.getString(FieldMap.SQLFIELDTYPE).equals(FieldMap.type_INT)){
            try{
                int i = rs.getInt(sqlfieldname);
                return new Integer(i);
            }catch(Exception e){
                return null;
            }
        }else if(field.getString(FieldMap.SQLFIELDTYPE).equals(FieldMap.type_TEXT)){
            String retval = rs.getString(sqlfieldname);
            return retval;
        }
        return null;
    }

    /**
     * getSearchSQL
     *
     * @return String
     */
    private String getSearchSQL(Table table) throws Exception {
        String retval = "select ";
        String whereclause = " where ";
        for(int i = 0; i < this.m_primarykeys.size();i++){
            FieldMap field = (FieldMap)this.m_primarykeys.get(i);
            String sqlfield = field.getString(FieldMap.SQLFIELDNAME);
            if(i > 0){
                retval += ",";
                whereclause += " and ";
            }
            retval += sqlfield;
            whereclause += "(" + sqlfield + " is not null or len(" + sqlfield + ") > 0)";
        }
        retval += " from " + table.getString(Table.SQLTABLE);
        if(table.getBoolean(Table.bool_MULTIFORM) == true){
            whereclause += " and form = \"" + table.getParent().getParent().getName()+
                    "\"";
        }
        return retval + whereclause;
    }

}
